package com.nassir.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

/**
 * @author caihua
 */
public class ExcelReader {
	private HSSFWorkbook wb = null;// book [includes sheet]

	private HSSFSheet sheet = null;

	private HSSFRow row = null;

	private int sheetNum = 0; // 第sheetnum个工作表

	private int rowNum = 0;

	private FileInputStream fis = null;

	private File file = null;

	public ExcelReader() {
	}

	public ExcelReader(File file) {
		this.file = file;
	}

	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}

	public void setSheetNum(int sheetNum) {
		this.sheetNum = sheetNum;
	}

	public void setFile(File file) {
		this.file = file;
	}

	/**
	 * 读取excel文件获得HSSFWorkbook对象
	 */
	public void open() throws IOException {
		fis = new FileInputStream(file);
		wb = new HSSFWorkbook(new POIFSFileSystem(fis));
		fis.close();
	}

	/**
	 * 返回sheet表数目
	 * 
	 * @return int
	 */
	public int getSheetCount() {
		int sheetCount = -1;
		sheetCount = wb.getNumberOfSheets();
		return sheetCount;
	}

	/**
	 * sheetNum下的记录行数
	 * 
	 * @return int
	 */
	public int getRowCount() {
		if (wb == null)
			System.out.println("=============>WorkBook为空");
		HSSFSheet sheet = wb.getSheetAt(this.sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}

	/**
	 * 读取指定sheetNum的rowCount
	 * 
	 * @param sheetNum
	 * @return int
	 */
	public int getRowCount(int sheetNum) {
		HSSFSheet sheet = wb.getSheetAt(sheetNum);
		int rowCount = -1;
		rowCount = sheet.getLastRowNum();
		return rowCount;
	}
	
	/**
	 * 读取指定sheetNum的名称
	 * 
	 * @param sheetNum
	 * @return String
	 */
	public String getSheetName(int sheetNum) {
		HSSFSheet sheet = wb.getSheetAt(sheetNum);
		return sheet.getSheetName();
		
	}

	/**
	 * 得到指定行的内容
	 * 
	 * @param lineNum
	 * @return String[]
	 */
	public Object[] readExcelLine(int lineNum) {
		return readExcelLine(this.sheetNum, lineNum);
	}

	/**
	 * 指定工作表和行数的内容
	 * 
	 * @param sheetNum
	 * @param lineNum
	 * @return String[]
	 */
	public Object[] readExcelLine(int sheetNum, int lineNum) {
		if (sheetNum < 0 || lineNum < 0)
			return null;
		Object[] strExcelLine = null;
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(lineNum);
			//int cellCount = row.getLastCellNum();
			strExcelLine = new Object[sheet.getRow(0).getLastCellNum()];
			for (int i = 0; i < strExcelLine.length; i++) {
				strExcelLine[i] = readStringExcelCell(lineNum, i);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelLine;
	}

	/**
	 * 读取指定列的内容
	 * 
	 * @param cellNum
	 * @return String
	 */
	public Object readStringExcelCell(int cellNum) {
		return readStringExcelCell(this.rowNum, cellNum);
	}

	/**
	 * 指定行和列编号的内容
	 * 
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public Object readStringExcelCell(int rowNum, int cellNum) {
		return readStringExcelCell(this.sheetNum, rowNum, cellNum);
	}

	/**
	 * 指定工作表、行、列下的内容
	 * 
	 * @param sheetNum
	 * @param rowNum
	 * @param cellNum
	 * @return String
	 */
	public Object readStringExcelCell(int sheetNum, int rowNum, int cellNum) {
		if (sheetNum < 0 || rowNum < 0)
			return null;
		Object strExcelCell = null;
		try {
			sheet = wb.getSheetAt(sheetNum);
			row = sheet.getRow(rowNum);

			if (row.getCell(cellNum) != null) { // add this condition
				// judge
				switch (row.getCell(cellNum).getCellType()) {
				case HSSFCell.CELL_TYPE_FORMULA:
					//System.out.println(row.getCell(cellNum).getCellFormula()
					//strExcelCell = row.getCell(cellNum).getCellFormula();//"FORMULA ";
					strExcelCell = row.getCell(cellNum).getNumericCellValue();
					break;
				case HSSFCell.CELL_TYPE_NUMERIC: {
					if(HSSFDateUtil.isCellDateFormatted(row.getCell(cellNum))){
						strExcelCell = HSSFDateUtil.getJavaDate( row.getCell(cellNum).getNumericCellValue()); 
					}
					else{
					   strExcelCell = row.getCell(cellNum).getNumericCellValue();
					}
				}
					break;
				case HSSFCell.CELL_TYPE_STRING:
					strExcelCell = row.getCell(cellNum).getStringCellValue();
					break;
				case HSSFCell.CELL_TYPE_BLANK:
					strExcelCell = "";
					break;
				default:
					strExcelCell = "";
					break;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return strExcelCell;

	}
    /**
     * 获取去整个excle数据
     * @return
     * @throws IOException
     */
	public  Map<String, List<Object []>> reader() throws IOException {
		 Map<String, List<Object []>> map=new HashMap<String, List<Object[]>>();
			this.open();
			int sheetCount=this.getSheetCount();
			for (int t = 0; t < sheetCount; t++) {
				this.setSheetNum(t); // 设置读取索引为0的工作表
				String sheetName=this.getSheetName(t);
				List<Object []> datalist=new ArrayList<Object[]>();
				int count = this.getRowCount();
				for (int i = 1; i <= count; i++) {
					Object [] rows = this.readExcelLine(i);
					datalist.add(rows);
				}
				map.put(sheetName, datalist);
			}
		return map;
	}
	
    /**
     * 根据sheet页名称获取sheet页数据
     * @return
     * @throws IOException
     */
	public  List<Object []> reader(String sheetName) throws IOException {
		List<Object []> datalist=new ArrayList<Object[]>();
			this.open();
			int sheetCount=this.getSheetCount();
			for (int t = 0; t < sheetCount; t++) {
				this.setSheetNum(t); // 设置读取索引为0的工作表
				if(this.getSheetName(t).equals(sheetName))
				{
					int count = this.getRowCount();
					for (int i = 1; i <= count; i++) {
						Object [] rows = this.readExcelLine(i);
						datalist.add(rows);
					}
					break;
				}
			}
		return datalist;
	}
	
	@SuppressWarnings("unchecked")
	public static void main(String args[]) throws IOException {
		
		
		//第一种
		File file = new File(ExcelWriter.class.getResource("").getPath()+"conf/workbookconfig.xml");
		System.out.println(ExcelWriter.class.getResource("").getPath()+"conf/workbookconfig.xml");
		//C:/Documents and Settings/Administrator/桌面/1234/SHA-HKG.xls
		ExcelReader readExcel = new ExcelReader(file);
		Map<String, List<Object []>> m=readExcel.reader();
		for (Iterator iterator = m.entrySet().iterator(); iterator.hasNext();) {
			Map.Entry<String, List<Object []>>entry=(Map.Entry<String, List<Object []>>)iterator.next();
			System.out.println("------"+entry.getKey()+"------");
			List<Object []> rows=m.get(entry.getKey());
			for (Object[] row : rows) {
				String line="";
				for (Object cell : row) {
					line +=cell+"=";
				}
				System.out.println(line);
				
			}
		}
		
		/*//第二种
		File file = new File("D:/国际TTS系统运价政策(xmlhead).xls");
		ExcelReader readExcel = new ExcelReader(file);
		List<Object []> list=readExcel.reader("add-on");
		for (Object[] row : list) {
			String line="";
			for (Object cell : row) {
				line +=cell+"=";
			}
			System.out.println(line);
		}*/
		
		
	}
	
}
